EasyExcel基本使用

2023-02-04 08:43| 来源: 网络整理| 查看: 265

官网地址: EasyExcel

本文代码: EasyExcelDemo

在开发中Excel的导入导出是非常常见的需求,最近在项目中用到了阿里的EasyExcel,简单学习下,还是比较容易上手的。下面记录下

EasyExcel介绍

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

导入依赖 com.alibaba easyexcel 2.1.2 导出

例如现在要导出如下Excel:

在这里插入图片描述

使用模型导出

使用模型导出时,EasyExcel会根据导出实体中 @ExcelProperty注解生成表头

模型注解说明 @HeadRowHeight：设置表头高度，作用域在类 @ExcelIgnoreUnannotated：忽略没有注解标记的字段，作用域在类 @ExcelIgnore：忽略指定的字段，作用域在字段 @ExcelProperty：指定导出列名和索引，作用域在字段 @ColumnWidth：设置列的宽度，作用域在字段 @NumberFormat：设置数值精度，作用域在字段，例：@NumberFormat(value = "#.00") @DateTimeFormat：格式化日期，作用域在字段，例：@DateTimeFormat(value = "yyyy-MM-dd") 建立模型 @Data @NoArgsConstructor @AllArgsConstructor @ColumnWidth(15) // 设置列宽 public class ExcelDemoDto { @ExcelProperty("字符串测试1") private String column1; @ExcelProperty("字符串测试2") private String column2; @ExcelProperty("数字测试") private BigDecimal number; @ExcelProperty("日期测试") @DateTimeFormat(value = "yyyy-MM-dd") private Date date; } 导出实现 public void easyWrite(HttpServletResponse response) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode防止中文乱码 String fileName = URLEncoder.encode("导出", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); ServletOutputStream outputStream = response.getOutputStream(); // 获取导出数据 List exportData = new ArrayList(); ExcelDemoDto excelDemoDto = new ExcelDemoDto("zjm","520", BigDecimal.valueOf(20),new Date()); exportData.add(excelDemoDto); // 初始化ExcelWriter ExcelWriter writer = EasyExcel.write(outputStream).build(); // 初始化一个sheet WriteSheet sheet1 = EasyExcel.writerSheet(0, "sheet1") .head(ExcelDemoDto.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .build();// EasyExcel自带的自动列宽拦截器 // 写入数据生成Excel文件 try { writer.write(exportData, sheet1); } catch (Exception e) { log.info("导出失败: {}",e.getCause()); throw e; } finally { writer.finish(); } }

不使用用模型导出

如果不使用模型,可以手动设置表头

public void easyWriteNoModel(HttpServletResponse response) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode防止中文乱码 String fileName = URLEncoder.encode("导出", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); ServletOutputStream outputStream = response.getOutputStream(); // 获取导出数据 List exportData = new ArrayList(); ExcelDemoDto excelDemoDto = new ExcelDemoDto("zjm","520", BigDecimal.valueOf(20),new Date()); exportData.add(excelDemoDto); // head List head = new ArrayList(); // 每一列都需要设置一个List List headColumn1 = Arrays.asList("字符串测试1"); List headColumn2 = Arrays.asList("字符串测试2"); List headColumn3 = Arrays.asList("数字测试"); List headColumn4 = Arrays.asList("日期测试"); head.add(headColumn1); head.add(headColumn2); head.add(headColumn3); head.add(headColumn4); // 初始化ExcelWriter ExcelWriter writer = EasyExcel.write(outputStream).build(); // 初始化一个sheet WriteSheet sheet1 = EasyExcel.writerSheet(0, "sheet1") .head(head) .build(); // 写入数据生成Excel文件 try { writer.write(exportData, sheet1); } catch (Exception e) { log.info("导出失败: {}",e.getCause()); throw e; } finally { writer.finish(); } } 导入

监听器实现

/** * 自定义监听器实现 * @param */ @Data @EqualsAndHashCode(callSuper = true) public class AnalysisEventListenerImpl extends AnalysisEventListener { private List datas = new ArrayList(); @Override public void invoke(E data, AnalysisContext context) { datas.add(data); } @Override public void doAfterAllAnalysed(AnalysisContext context) { } }

读取工具类 EasyExcelUtil

public static List readExcelByModel(MultipartFile file,Class clazz,int sheetNo,int headRowNumber) throws IOException { List list = new ArrayList(); InputStream inputStream = null; try { // 获取输入流 inputStream = file.getInputStream(); // 数据收集器 AnalysisEventListenerImpl listener = new AnalysisEventListenerImpl(); ExcelReader excelReader = EasyExcel.read(inputStream, listener).build(); // 第一个sheet读取类型 ReadSheet readSheet = EasyExcel.readSheet(sheetNo).head(clazz).headRowNumber(headRowNumber).build(); // 开始读取第一个sheet excelReader.read(readSheet); list = listener.getDatas(); } catch (IOException e) { // throw new RuntimeException("excel解析出错"); log.info("解析出错: " + e.getCause()); } finally { // 记得关流 if (inputStream != null) { inputStream.close(); } } return list; }

导入实现

public List easyRead(MultipartFile file) throws Exception { try { List list = EasyExcelUtil.readExcelByModel(file, ExcelDemoDto.class); return list; } catch (IOException e) { throw new Exception("Excel解析出错"); } } 多sheet导出 public void multiSheetWrite(HttpServletResponse response) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode防止中文乱码 String fileName = URLEncoder.encode("导出", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); ServletOutputStream outputStream = response.getOutputStream(); // 获取导出数据 List exportData1 = new ArrayList(); ExcelDemoDto excelDemoDto = new ExcelDemoDto("zjm","520", BigDecimal.valueOf(20),new Date()); exportData1.add(excelDemoDto); // 初始化ExcelWriter ExcelWriter writer = EasyExcel.write(outputStream).build(); // 初始化一个sheet1 WriteSheet sheet1 = EasyExcel.writerSheet(0, "sheet1") .head(ExcelDemoDto.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .build();// EasyExcel自带的自动列宽拦截器 WriteSheet sheet2 = EasyExcel.writerSheet(1, "sheet2") .head(ExcelDemoDto.class) .build(); // 写入数据生成Excel文件 try { writer.write(exportData1, sheet1); writer.write(exportData1, sheet2); } catch (Exception e) { log.info("导出失败: {}",e.getCause()); throw e; } finally { writer.finish(); } } 多sheet导入 public Map multiSheetRead(MultipartFile file) throws IOException { List list1 = EasyExcelUtil.readExcelByModel(file, ExcelDemoDto.class, 0, 1); List list2 = EasyExcelUtil.readExcelByModel(file, ExcelDemoDto.class, 1, 1); Map map = new HashMap(); map.put(0,list1); map.put(1,list2); return map; } 复杂表头导出

在实际业务中,往往导入导出的Excel表头十分复杂,例如下面这样的在这里插入图片描述

使用模型设置

@Data public class ExcelMultiHeadDemoDto { @ExcelProperty({ "序号","序号","序号","序号"}) private String no; @ExcelProperty({ "字段1","字段1","字段1","字段1"}) private String column1; @ExcelProperty({ "大标题1","大标题1-1","大标题1-1","大标题1-1"}) private String column2; @ExcelProperty({ "大标题1","大标题1-2","大标题1-2","大标题1-2"}) private String column3; @ExcelProperty({ "大标题1","大标题1-3","大标题1-3","大标题1-4"}) private BigDecimal column4; }

效果如下

在这里插入图片描述

复杂表头导入

EasyExcel在解析数据时,默认表头为1,如多表头是多行,需要设置 rowHeadNumber参数,若不设置,EasyExcel会将表头也解析,在使用

模型读取时极有可能出现类型转换错误

public List multiHeadRead(MultipartFile file) { try { List list = EasyExcelUtil.readExcelByModel(file, ExcelMultiHeadDemoDto.class,0,4); return list; } catch (IOException e) { log.info("Excel解析出错"); } return null; }

【本文地址】

公司简介

联系我们